feidlambda II

Exploring Dynamic Array + User Defined Lambda (UDL) with LAMBDA

Taruma Sakti Megariansyah

2022-10-25

Agenda

Agenda untuk feidlambda II: Exploring Dynamic Array + User Defined Lambda (UDL) with LAMBDA

  • Review Daftar Istilah & Dynamic Array Formula
  • Logical
  • Dynamic Array Formula (DAF)
  • Fungsi Baru (2019-2021)
  • User Defined Lambda (UDL) dengan LAMBDA

1 Review

Daftar Istilah

2 Logical

Fungsi dan Operator Logical

AND

Syntax

=AND(logical1, [logical2], ...)
  • Fungsi logika, untuk menentukan apakah semua kondisi dalam sebuah tes adalah TRUE. 1
  • Menghasilkan nilai TRUE ketika dua (atau lebih) bernilai TRUE. Jika terdapat satu atau lebih nilai FALSE maka menghasilkan nilai FALSE.
  • Jika input berupa logical vector saja (AND(logical_vector)), maka akan mengevaluasi seluruh elemen pada logical vector (AND(elemen_1, elemen_2, ...)).

Operator AND (*)

Syntax

=logical_vector1 * logical_vector2
  • Mengevaluasi AND pada setiap elemen dengan vector/array.
    ={AND(elemen1_vector1, elemen1_vector2, ...), AND(elemen2_vector1, elemen2_vector2, ...), ...}.
  • Menghasilkan numerical vector yang bernilai 0 (FALSE) atau 1 (TRUE). Contohnya: {1, 0, 1}
  • Gunakan numerical_vector=1 untuk menghasilkan logical vector ({TRUE, FALSE, TRUE})

OR

=OR(logical1, [logical2], ...)
  • Fungsi logika, untuk menentukan apakah salah satu kondisi dalam sebuah tes adalah TRUE. 1
  • Menghasilkan nilai TRUE ketika salah satu bernilai TRUE.
  • Jika input berupa logical vector saja (OR(logical_vector)), maka akan mengevaluasi seluruh elemen pada logical vector (OR(elemen_1, elemen_2, ...)).

Operator OR (+)

Syntax

=logical_vector1 * logical_vector2
  • Mengevaluasi OR pada setiap elemen dengan vector/array.
    ={OR(elemen1_vector1, elemen1_vector2, ...), OR(elemen2_vector1, elemen2_vector2, ...), ...}.
  • Menghasilkan numerical vector yang bernilai 0 (FALSE) atau 1 (TRUE). Contohnya: {1, 0, 1}
  • Gunakan numerical_vector=1 untuk menghasilkan logical vector ({TRUE, FALSE, TRUE})

Fungsi IS*

Fungsi IS*

SYNTAX

=IS*(value)
  • Memeriksa nilai tertentu dan mengembalikan TRUE atau FALSE bergantung pada hasilnya. 1
  • ISBLANK bernilai TRUE jika sel kosong.
  • ISNUMBER bernilai TRUE jika sel berisikan angka.

Koleksi Fungsi IS* (1)

Periksa nilai error / kosong

  • ISNA(...): Cek nilai apakah #N/A.
  • ISERR(...): Cek nilai apakah error selain #N/A (#VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, atau #NULL!).
  • ISERROR(...): Cek nilai apakah error (seluruh jenis error).
  • ISBLANK(...): Cek nilai apakah kosong.

Koleksi Fungsi IS* (2)

Periksa nilai jenis sel

  • ISLOGICAL(...): Cek nilai apakah logical (TRUE atau FALSE).
  • ISNUMBER(...): Cek nilai apakah numeric (\(\mathbb{R}\)).
  • ISTEXT(...): Cek nilai apakah teks. Dan ISNONTEXT(...): Cek niai apakah bukan teks (number, logical, error)
  • ISFORMULA(...): Cek nilai apakah formula.

Koleksi Fungsi IS* (3)

Periksa nilai angka

  • ISEVEN(...): Cek nilai apakah bilangan genap.
  • ISODD(...): Cek nilai apakah bilangan ganjil.

3 Dynamic Array Formula (DAF)

Menyeleksi/memilih data

  • FILTER(...): Fungsi FILTER memungkinkan Anda memfilter rentang data berdasarkan kriteria yang ditetapkan. 1
=FILTER(array, include, [if_empty])
  • UNIQUE(...): Fungsi UNIQUE menghasilkan daftar nilai yang unik dalam daftar atau rentang. 2
=UNIQUE(array, [by_col], [exactly_once])

Membangkitkan data

  • SEQUENCE(...): Fungsi SEQUENCE memungkinkan Anda menghasilkan daftar angka yang berurutan dalam larik seperti \({1, 2, 3, 4, \dots}\). 1
=SEQUENCE(rows, [columns], [start], [step])
  • RANDARRAY(...): Fungsi RANDARRAY menghasilkan larik angka acak. Anda dapat menentukan jumlah baris dan kolom yang akan diisi, nilai minimum dan maksimum, dan apakah fungsi mengembalikan bilangan bulat atau nilai desimal. 2
=RANDARRAY([rows], [columns], [min], [max], [whole_number])

Menyusun data

  • SORT(...): Fungsi SORT mengurutkan konten rentang atau larik. 1
=SORT(array, [sort_index], [sort_order], [by_col])
  • SORTBY(...): Fungsi SORTBY mengurutkan konten rentang atau larik berdasarkan nilai dalam rentang atau larik yang terkait. 2
=SORTBY(array, by_array1, [sort_order1], [by_array2], [sort_order2], ...)

Mencari data

  • XLOOKUP(...): Fungsi XLOOKUP mencari rentang atau array, lalu mengembalikan item yang terkait dengan kecocokan pertama yang ditemukannya. Jika tidak ada kecocokan, XLOOKUP dapat mengembalikan kecocokan terdekat (perkiraan). 1
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
  • XMATCH(...): Fungsi XMATCH mengembalikan posisi relatif item dalam array atau rentang sel. 2
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

4 Fungsi Baru (2019-2021)

Excel 2019

Penggabungan Teks

  • CONCAT(...): Fungsi CONCAT menggabungkan teks dari beberapa rentang dan/atau string, tetapi tidak menyediakan argumen pemisah atau IgnoreEmpty. 1
=CONCAT(text1, [text2], ...)
  • TEXTJOIN(...): Fungsi TEXTJOIN menggabungkan teks dari beberapa rentang dan/atau string, serta menyertakan pemisah yang Anda tentukan antara tiap nilai teks yang akan digabungkan. Jika pemisah adalah string teks kosong, fungsi ini akan secara efektif menggabungkan rentang. 2
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

KOLEKSI IF*

  • IFS(...): Fungsi IFS memeriksa apakah satu atau beberapa kondisi terpenuhi dan mengembalikan nilai yang sesuai dengan kondisi TRUE pertama. IFS dapat menggantikan beberapa pernyataan IF yang bertumpuk, dan jauh lebih mudah dibaca dengan beberapa kondisi. 1
=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], [logical_test3, value_if_true3], ..., value_if_false)
  • MINIFS(...) / MAXIFS(...): Fungsi MINIFS mengembalikan nilai minimal di antara sel yang ditentukan oleh kumpulan persyaratan atau kriteria tertentu / Fungsi MAXIFS mengembalikan nilai maksimal di antara sel yang ditentukan oleh kumpulan persyaratan atau kriteria tertentu. 2 3
=[MIN/MAX]IFS([min/max]_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

SWITCH

  • SWITCH(...): Fungsi SWITCH mengevaluasi satu nilai (disebut ekspresi) terhadap daftar nilai, dan mengembalikan hasil yang terkait dengan nilai cocok pertama. Jika tidak terdapat kecocokan, nilai default opsional mungkin akan dikembalikan. 1
=SWITCH(expression, value1, result1, [default or value2, result2], ..., [default or value3, result3])

EXCEL 2021

LET

  • LET(...): Fungsi LET menetapkan nama ke hasil perhitungan. Ini memungkinkan menyimpan perhitungan menengah, nilai, atau menetapkan nama di dalam rumus. Nama ini hanya berlaku di dalam lingkup LET fungsi. Mirip dengan variabel dalam pemrograman, LET dilakukan melalui Excel rumus asli Anda. 1
=LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name2], ..., calculation)

5 User Defined Lambda (UDL) dengan LAMBDA

Mengenal LAMBDA dan UDL

  • LAMBDA(...): Gunakan fungsi LAMBDA untuk membuat fungsi kustom yang dapat digunakan kembali dan memanggilnya dengan nama yang mudah dikenali. Fungsi baru tersedia di seluruh buku kerja dan disebut seperti fungsi asli Excel. 1
=LAMBDA(parameter1, [parameter2, parameter3, ...], calculation)

LAMBDA dan LET

  • LAMBDA dan LET biasanya digunakan bersamaan untuk memudahkan pembuatan UDL ataupun membaca/troubleshooting.
  • LAMBDA berfungsi menerima input dan membungkusnya sebagai fungsi baru di Excel.
  • LET digunakan untuk melakukan perhitungan/kalkulasi setelah menerima input dari LAMBDA.

Menggabungkan LAMBDA dan LET

=LAMBDA(parameter1, [parameter2, parameter3, ...], calculation)

Menggabungkan LAMBDA dan LET

=LAMBDA(
  parameter1, 
  [parameter2, parameter3, ...],
  calculation
)

Menggabungkan LAMBDA dan LET

=LAMBDA(
  parameter1, 
  [parameter2, parameter3, ...],
  LET(
    name1, value1,
    [name2, value2, name3, value3, ...],
    calculation
  )
)

Contoh LAMBDA dan LET

Contoh LAMBDA dan LET

  • Mengurangi setiap elemen dari numeric vector (\(\mathbf{x}\)) dengan setengah dari rata-ratanya (\(\frac{\text{mean}(\mathbf{x})}{2}\)).
  • \(\mathbf{y} \leftarrow \mathbf{x} - \frac{\text{mean}(\mathbf{x})}{2}\)

Menentukan input LAMBDA

\[ \mathbf{y} \leftarrow \mathbf{x} - \frac{\text{mean}(\mathbf{x})}{2} \]

=LAMBDA(
  parameter1, 
  [parameter2, parameter3, ...],
  calculation
)

Menentukan input LAMBDA

\[ \mathbf{y} \leftarrow \mathbf{x} - \frac{\text{mean}(\mathbf{x})}{2} \]

=LAMBDA(
  vector,
  calculation
)

Kalkulasi dengan LET

\[ \mathbf{y} \leftarrow \mathbf{x} - \frac{\text{mean}(\mathbf{x})}{2} \]

=LAMBDA(
  vector,
  LET(
    name1, value1,
    [name2, value2, name3, value3, ...],
    calculation
  )
)

Kalkulasi dengan LET

\[ \mathbf{y} \leftarrow \mathbf{x} - \frac{\text{mean}(\mathbf{x})}{2} \]

=LAMBDA(
  vector,
  LET(
    x, vector,
    [name2, value2, name3, value3, ...],
    calculation
  )
)

Kalkulasi dengan LET

\[ \mathbf{y} \leftarrow \mathbf{x} - \frac{\text{mean}(\mathbf{x})}{2} \]

=LAMBDA(
  vector,
  LET(
    ..., ...,
    [name2, value2, name3, value3, ...],
    calculation
  )
)

Kalkulasi dengan LET

\[ \mathbf{y} \leftarrow \mathbf{x} - \frac{\text{mean}(\mathbf{x})}{2} \]

=LAMBDA(
  vector,
  LET(
    avg, AVERAGE(vector),
    [name2, value2, name3, value3, ...],
    calculation
  )
)

Kalkulasi dengan LET

\[ \mathbf{y} \leftarrow \mathbf{x} - \frac{\text{mean}(\mathbf{x})}{2} \]

=LAMBDA(
  vector,
  LET(
    avg, AVERAGE(vector),
    halfavg, avg/2,
    ..., ...,
    calculation
  )
)

Kalkulasi dengan LET

\[ \mathbf{y} \leftarrow \mathbf{x} - \frac{\text{mean}(\mathbf{x})}{2} \]

=LAMBDA(
  vector,
  LET(
    avg, AVERAGE(vector),
    halfavg, avg/2,
    y, vector - halfavg,
    calculation
  )
)

Kalkulasi dengan LET

\[ \mathbf{y} \leftarrow \mathbf{x} - \frac{\text{mean}(\mathbf{x})}{2} \]

=LAMBDA(
  vector,
  LET(
    avg, AVERAGE(vector),
    halfavg, avg/2,
    y, vector - halfavg,
    y
  )
)

Kalkulasi dengan LET

\[ \mathbf{y} \leftarrow \mathbf{x} - \frac{\text{mean}(\mathbf{x})}{2} \]

=LAMBDA(
  vector,
  LET(
    avg, AVERAGE(vector),
    halfavg, avg/2,
    vector - halfavg
  )
)

Kalkulasi dengan LET

\[ \mathbf{y} \leftarrow \mathbf{x} - \frac{\text{mean}(\mathbf{x})}{2} \]

=LAMBDA(
  vector,
  LET(
    avg, AVERAGE(vector),
    halfavg, avg/2,
    vector - halfavg
  )
)

Pengembangan lebih lanjut

Validasi input

\[ \mathbf{y} \leftarrow \mathbf{x} - \frac{\text{mean}(\mathbf{x})}{2} \]

=LAMBDA(
  vector,
  LET(
    avg, AVERAGE(vector),
    halfavg, avg/2,
    vector - halfavg
  )
)

Validasi input

\[ \mathbf{y} \leftarrow \mathbf{x} - \frac{\text{mean}(\mathbf{x})}{2} \]

=LAMBDA(
  vector,
  LET(
    vector_clean, FILTER(vector, ISNUMBER(vector)),
    avg, AVERAGE(vector_clean),
    halfavg, avg/2,
    vector - halfavg
  )
)

Refactoring

\[ \mathbf{y} \leftarrow \mathbf{x} - \frac{\text{mean}(\mathbf{x})}{2} \]

=LAMBDA(
  vector,
  LET(
    vector_clean, FILTER(vector, ISNUMBER(vector)),
    avg, AVERAGE(vector_clean),
    halfavg, avg/2,
    vector - halfavg
  )
)

Refactoring

\[ \mathbf{y} \leftarrow \mathbf{x} - \frac{\text{mean}(\mathbf{x})}{2} \]

=LAMBDA(
  vector,
  LET(
    vector_clean, FILTER(vector, ISNUMBER(vector)),
    halfavg, AVERAGE(vector_clean) / 2,
    vector - halfavg
  )
)

Optional argument/parameter

\[ \mathbf{y} \leftarrow \mathbf{x} - \frac{\text{mean}(\mathbf{x})}{n} \]

=LAMBDA(
  vector,
  [n],
  LET(
    n, IF(ISOMITTED(n), 2, n),
    vector_clean, FILTER(vector, ISNUMBER(vector)),
    halfavg, AVERAGE(vector_clean) / n,
    vector - halfavg
  )
)

Optional argument/parameter

\[ \mathbf{y} \leftarrow \mathbf{x} - \frac{\text{mean}(\mathbf{x})}{n} \]

=LAMBDA(
  vector,
  [n],
  LET(
    n, IF(ISOMITTED(n), 2, n),
    vector_clean, FILTER(vector, ISNUMBER(vector)),
    halfavg, AVERAGE(vector_clean) / n,
    vector - halfavg
  )
)

Penggunaan Formula

\[ \mathbf{y} \leftarrow \mathbf{x} - \frac{\text{mean}(\mathbf{x})}{n} \]

  • Formula tersebut disimpan sebagai HALFAVG di Formulas Name Manager.
  • Sintaksnya =HALFAVG(vector, [n]).
  • =HALFAVG(D5:D10) (maka \(n=2\)).
  • =HALFAVG(D5#, 4) (maka \(n=4\)).

Advanced Formula Environment (AFE)

  • Excel add-ins yang memudahkan mengatur formula pada lembar kerja.
  • Alternatif Formulas Name Manager.
  • Memiliki fitur syntax highlighting dan shortcut umum untuk coding.
  • Dapat import UDL hasil dari orang lain. 1

Berbagi UDL

Terima Kasih

Taruma Sakti Megariansyah, hi@taruma.info

© 2022 Taruma Sakti Megariansyah. CC-BY-NC-SA 4.0.